package dao.impl;

import dao.BaseDao;
import dao.BooksDao;
import entity.Books;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BooksDaoImpl extends BaseDao implements BooksDao {
    Connection conn;
    PreparedStatement pstmt;
    ResultSet rs;

    @Override
    public int insertBooks(Books books) {
        int r = 0;
        conn = getConnection();
        String sql = "insert into `timesequence`.`books` (`uid`, `sid`, `aid`, `wid`, `amount`, `remark`,`time`) values (?,?,?,?,?,?,?)";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,books.getUid());
            pstmt.setInt(2,books.getSid());
            pstmt.setInt(3,books.getAid());
            pstmt.setInt(4,books.getWid());
            pstmt.setDouble(5,books.getAmount());
            pstmt.setString(6,books.getRemark());
            pstmt.setString(7,books.getTime());
            r = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,pstmt,null);
        }
        return r;
    }

    @Override
    public int deleteBooks(int bid) {
        int r = 0;
        conn = getConnection();
        String sql = "delete from books where bid = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,bid);
            r = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,pstmt,null);
        }
        return r;
    }

    @Override
    public int updateBooks(Books books) {
        int r = 0;
        conn = getConnection();
        String sql = "update books set aid = ?,wid = ?,amount=?,remark=? where bid = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,books.getAid());
            pstmt.setInt(2,books.getWid());
            pstmt.setDouble(3,books.getAmount());
            pstmt.setString(4,books.getRemark());
            pstmt.setInt(5,books.getBid());
            r = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,pstmt,null);
        }
        return r;
    }

    @Override
    public ResultSet selectBooksList(int uid,String time) {
        conn = getConnection();
        String sql = "SELECT a.approach approach,a.`url` url,s.`status` status,u.`uname` uname,b.*,wt.`wtname` wtname,w.wname FROM approach a,`status` s,`user` u,books b,wallet w,wallettype wt\n" +
                "WHERE a.`sid`=s.`sid`\n" +
                "AND u.`uid` = b.`uid`\n" +
                "AND b.`aid` = a.`aid`\n" +
                "AND w.`wtid` = wt.`wtid`\n" +
                "AND b.`wid` = w.`wid`\n" +
                "AND u.uid = ?\n" +
                "AND  TIME LIKE CONCAT('%',?,'%')\n" +
                "ORDER BY b.`time` DESC;";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setString(2,time);
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectBooks(int bid) {
        conn = getConnection();
        String sql = "select * from books where bid = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,bid);
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectAmount(int uid, int sid, String time) {
        String sql = "SELECT SUM(amount) FROM books WHERE uid = ? and sid = ? \n" +
                "AND  TIME LIKE CONCAT('%',?,'%');";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            pstmt.setString(3,time);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectCurrentMonthAmount(int uid) {
        String sql = "SELECT SUM(amount) amount,sid FROM books WHERE uid = ? AND DATE_FORMAT(TIME,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY sid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectSumAmount(int uid, int sid) {
        String sql = "SELECT b.aid,a.`approach`,SUM(b.amount) amount FROM books b,approach a " +
                "WHERE a.`aid`=b.`aid` AND b.`uid`= ? AND b.`sid` = ? GROUP BY aid;";
        conn = getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectToday(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND TO_DAYS(TIME) = TO_DAYS(NOW())\n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectYesterday(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "and TO_DAYS(NOW()) - TO_DAYS(TIME) = 1 \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectThisWeek(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "and YEARWEEK(DATE_FORMAT(TIME,'%Y-%m-%d')) = YEARWEEK(NOW()) \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectLastWeek(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND YEARWEEK(DATE_FORMAT(TIME,'%Y-%m-%d')) = YEARWEEK(NOW())-1 \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectNearlySevenDays(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(TIME) \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectThisMonth(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND DATE_FORMAT(TIME,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectLastMonth(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(TIME,'%Y%m')) = 1 \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectNearlyThirtyDays(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(TIME) \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectNearlySixMonth(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND TIME BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW() \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectThisQuarter(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND QUARTER(TIME) = QUARTER(NOW()) \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectLastQuarter(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND  QUARTER(TIME) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectThisYear(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND YEAR(TIME)=YEAR(NOW()) \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet selectLastYear(int uid, int sid) {
        String sql = "SELECT aid,SUM(books.amount) amount \n" +
                "FROM books WHERE uid = ? AND sid = ? \n" +
                "AND YEAR(TIME) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) \n" +
                "GROUP BY aid;";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setInt(2,sid);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }
}
